package saleMarket.dao;

import saleMarket.domain.Customer;
import saleMarket.domain.Shop;
import saleMarket.domain.ShopOwner;
import saleMarket.domain.Warehouse;
import util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.TreeSet;

/**
 * @author 付饶
 * @version 1.0  12-07-2020
 */
public class ShopOwnerDao {
    /**
     *单例模式
     *该类负责创建自己的对象，同时确保只有单个对象被创建。
     * 这个类提供了一种访问其唯一的对象的方式，可以直接访问，不需要实例化该类的对象。
     */
    private static ShopOwnerDao shopOwnerDao = new ShopOwnerDao();
    private ShopOwnerDao(){}

    public static ShopOwnerDao getInstance(){return shopOwnerDao;}
    /**
     * @param conn      连接
     * @param conn 来自Service的Connection对象
     * @return ShopOwner对象
     * @throws SQLException
     */
    //查询单个shopOwner方法
    public ShopOwner find(Integer id, Connection conn) throws SQLException {
        ShopOwner shopOwner = null;
        //编辑预编译语句
        PreparedStatement find_pstmt = conn.prepareStatement("SELECT * FROM shopowner WHERE id = ?");
        //设置预编译参数
        find_pstmt.setInt(1,id);
        //执行查询并获取查询结果
        ResultSet find_resultSet = find_pstmt.executeQuery();
        //赋值
        if (find_resultSet.next()){
            String no = find_resultSet.getString("no");
            String name = find_resultSet.getString("name");
            String phoneNumber = find_resultSet.getString("phoneNumber");
            String belongs = find_resultSet.getString("belongs");
            //创建相应的店主对象
            shopOwner = new ShopOwner(id,no,name,phoneNumber,belongs);
            System.out.println(shopOwner);
        }
        //释放资源
        JdbcHelper.close(find_resultSet,find_pstmt);
        //返回查询结果
        return shopOwner;
    }
    /**
     * @param conn       连接
     * @param conn 来自Service的Connection对象
     * @return collection<ShopOwner></>
     * @throws SQLException
     */
    //查询所有shopOwner方法
    public Collection<ShopOwner> findAll(Connection conn) throws SQLException {
          Collection<ShopOwner> shopOwners = new ArrayList<>();
        //编辑预编译语句
        PreparedStatement findAll_pstmt = conn.prepareStatement("SELECT * FROM shopOwner");
        //执行查询并获取查询结果
        ResultSet findAll_resultSet = findAll_pstmt.executeQuery();
        //赋值
        while (findAll_resultSet.next()){
            Integer id = findAll_resultSet.getInt("id");
            String no = findAll_resultSet.getString("no");
            String name = findAll_resultSet.getString("name");
            String phoneNumber = findAll_resultSet.getString("phoneNumber");
            String belongs = findAll_resultSet.getString("belongs");
            //创建相应的shopOwner对象
            ShopOwner shopOwner = new ShopOwner(id,no,name,phoneNumber,belongs);
            //将查询出的单个shopOwner添加进集合中
            shopOwners.add(shopOwner);
        }
        //释放资源
        JdbcHelper.close(findAll_resultSet,findAll_pstmt);
        //返回查询结果
        return shopOwners;
    }
    /**
     * @param shopOwner 要增加到数据库表中的ShopOwner对象
     * @param conn 来自Service的Connection对象
     * @return 成功增加true，失败：false
     * @throws SQLException
     */
    //增加单个店主方法
    public boolean add(ShopOwner shopOwner,Connection conn) throws SQLException{
        //编辑预编译语句
        PreparedStatement add_pstmt = conn.prepareStatement(
                "INSERT INTO shopOwner(id,name,no,phoneNumber,belongs)VALUES (?,?,?,?,?)");
        //设置预编译参数
        add_pstmt.setInt(1,shopOwner.getId());
        add_pstmt.setString(2,shopOwner.getName());
        add_pstmt.setString(3,shopOwner.getNo());
        add_pstmt.setString(4,shopOwner.getPhoneNumber());
        add_pstmt.setString(5,shopOwner.getBelongs());

        //获取执行结果
        int affectedRow = add_pstmt.executeUpdate();
        //释放资源
        JdbcHelper.close(add_pstmt);
        //返回执行结果
        return affectedRow>0;
    }
    /**
     * @param shopOwner 要更新到数据库表中的ShopOwner对象
     * @param conn 来自Service的Connection对象
     * @return update成功返回true，反之，false
     * @throws SQLException
     */
    //更新顾客信息方法
    public boolean update(ShopOwner shopOwner,Connection conn) throws SQLException{
        //编辑预编译语句
        PreparedStatement update_pstmt = conn.prepareStatement("UPDATE ShopOwner SET" +
                " name=?,no=?,phoneNumber=?,belongs=? where id=?");
        //设置预编译参数
        update_pstmt.setInt(5,shopOwner.getId());
        update_pstmt.setString(1,shopOwner.getName());
        update_pstmt.setString(2,shopOwner.getNo());
        update_pstmt.setString(3,shopOwner.getPhoneNumber());
        update_pstmt.setString(4,shopOwner.getBelongs());


        //获取执行结果
        int affectedRow = update_pstmt.executeUpdate();
        //释放资源
        JdbcHelper.close(update_pstmt);
        //返回执行结果
        return affectedRow>0;
    }
    /**
     * @param id 要删除数据库表中的ShopOwner对象
     * @param conn 来自Service的Connection对象
     * @return 删除成功返回true，反之，false
     * @throws SQLException
     */
    //删除顾客方法
    public boolean delete(Integer id,Connection conn)throws SQLException{
        //设置预编译语句
        PreparedStatement delete_pstmt = conn.prepareStatement("DELETE FROM shopOwner WHERE id=?");
        //设置预编译参数
        delete_pstmt.setInt(1,id);
        //获取执行结果
        int affectedRow = delete_pstmt.executeUpdate();
        //释放资源
        JdbcHelper.close(delete_pstmt);
        //返回执行结果
        return affectedRow>0;
    }

}
